USE [Test_Events_Temp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spPrepareSnapshotData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spPrepareSnapshotData]
GO

CREATE PROCEDURE [dbo].[spPrepareSnapshotData]
@ChangedSince datetime
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @transaction_isolation_level SMALLINT;

	SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

    print('initial transaction_isolation_level: ' + STR(@transaction_isolation_level))

	

	EXECUTE dbo.spPrepareTables


	DECLARE @Log TABLE
	(
		Description nvarchar(max) NOT NULL,
		ElapsedTime int NOT NULL,
		RowsAffected int NOT NULL
	)

	declare @totalTime datetime
	declare @startTime datetime
	declare @endTime datetime
	declare @elapsedTime int
	declare @rowsAffected int

	select @totalTime = getdate()
	select @startTime = getdate()

	declare @LastSyncDate datetime
	declare @SyncAll bit = 1
	declare @LastUpdate datetime
	declare @DateTimeMinValue datetime = '1753-01-01'
	declare @sourceEventsDatabase nvarchar(max) = 'Test_Events'


	TRUNCATE TABLE CompanyProfiles
	INSERT INTO CompanyProfiles
	EXECUTE(@sourceEventsDatabase + '.dbo.spGetCompanyProfiles')

	set @rowsAffected = @@ROWCOUNT

	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	insert into @Log (Description, ElapsedTime, RowsAffected)
	values ('Extract Published Companies', @elapsedTime, @rowsAffected)
	select @startTime = getdate()


	SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
	BEGIN TRANSACTION;

	SELECT @transaction_isolation_level = transaction_isolation_level
	FROM sys.dm_exec_requests
	WHERE session_id = @@spid;

	print('after SET TRANSACTION ISOLATION LEVEL SNAPSHOT' + STR(@transaction_isolation_level))


	
	declare @MaxLastUpdate datetime

	select @MaxLastUpdate = max(LastUpdate) from [dbo].[CompanyProfiles]


	SELECT TOP(1) @LastUpdate = LastUpdate FROM [dbo].[SyncLog] ORDER BY LastSyncDate DESC

	IF (@ChangedSince = @DateTimeMinValue) SET @LastUpdate = NULL


	DECLARE @SelectedCompaniesCount int = 0

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	IF @LastUpdate IS NULL 
		BEGIN
			INSERT INTO @SelectedCompanies
			SELECT CompanyId AS Id FROM CompanyProfiles

			set @SelectedCompaniesCount = @@ROWCOUNT

			insert into @Log (Description, ElapsedTime, RowsAffected)
			values (str(@SelectedCompaniesCount) + ' published companies changed since the beginning of time', 0, @SelectedCompaniesCount)
		END
	ELSE
		BEGIN
			INSERT INTO @SelectedCompanies
			SELECT CompanyId AS Id FROM CompanyProfiles 
			WHERE LastUpdate > @LastUpdate

			set @SelectedCompaniesCount = @@ROWCOUNT

			insert into @Log (Description, ElapsedTime, RowsAffected)
			values (str(@SelectedCompaniesCount) + ' published companies changed since ' +  CONVERT(nvarchar(30), @LastUpdate, 126), 0, @SelectedCompaniesCount)
		END


	IF (@SelectedCompaniesCount) > 0
	BEGIN

		-- STEP 1 -> Extract data
		
		DECLARE @CompanyIds nvarchar(max)
		DECLARE @XML XML
		
		SET @XML = (SELECT * FROM @SelectedCompanies FOR XML RAW (''), ROOT('CompanyIds'), ELEMENTS, TYPE)
	    SET @CompanyIds = CONVERT(NVARCHAR(MAX), @XML, 1)


		--===== Event Indicator Templates ========================================================================

		TRUNCATE TABLE [dbo].[EventIndicatorTemplates]
		INSERT INTO [dbo].[EventIndicatorTemplates]
		EXECUTE [Test_Events].[dbo].[spGetEventIndicatorTemplates]

		set @rowsAffected = @@ROWCOUNT

		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Extract Event Indicator Templates', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		--===== Event Indicators ========================================================================

		DELETE PublishedEventIndicators FROM dbo.PublishedEventIndicators AS PublishedEventIndicators
		LEFT OUTER JOIN dbo.CompanyProfiles AS CompanyProfiles ON PublishedEventIndicators.CompanyId = CompanyProfiles.CompanyId
		WHERE  (CompanyProfiles.CompanyId IS NULL)   
	
		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete event indicators that do not belong to current companies', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		IF (@LastUpdate IS NULL) BEGIN
			TRUNCATE TABLE [dbo].[PublishedEventIndicators] END 
		ELSE BEGIN
			DELETE PublishedEventIndicators FROM dbo.PublishedEventIndicators AS PublishedEventIndicators
			INNER JOIN @SelectedCompanies AS SelectedCompanies ON PublishedEventIndicators.CompanyId = SelectedCompanies.Id
		END

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete event indicators that are going to be updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		INSERT INTO [dbo].[PublishedEventIndicators]
		EXECUTE [Test_Events].[dbo].[spGetEventIndicatorsByCompanyIds2] @CompanyIds, @MaxLastUpdate

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Events indicators have been updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		--===== Event Indicators ========================================================================

		DELETE PublishedEventIndicatorComments FROM dbo.PublishedEventIndicatorComments AS PublishedEventIndicatorComments
		LEFT OUTER JOIN dbo.CompanyProfiles AS CompanyProfiles ON PublishedEventIndicatorComments.CompanyId = CompanyProfiles.CompanyId
		WHERE  (CompanyProfiles.CompanyId IS NULL)   
	
		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete event indicator comments that do not belong to current companies', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		IF (@LastUpdate IS NULL) BEGIN
			TRUNCATE TABLE [dbo].[PublishedEventIndicatorComments] END 
		ELSE BEGIN
			DELETE PublishedEventIndicatorComments FROM dbo.PublishedEventIndicatorComments AS PublishedEventIndicatorComments
			INNER JOIN @SelectedCompanies AS SelectedCompanies ON PublishedEventIndicatorComments.CompanyId = SelectedCompanies.Id
		END

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete event indicator comments that are going to be updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		INSERT INTO [dbo].[PublishedEventIndicatorComments]
		EXECUTE [Test_Events].[dbo].[spGetEventIndicatorCommentsByCompanyIds2] @CompanyIds, @MaxLastUpdate

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Events indicator comments have been updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		--===== Incidents ========================================================================

		DELETE PublishedIncidents FROM dbo.PublishedIncidents AS PublishedIncidents
		LEFT OUTER JOIN dbo.CompanyProfiles AS CompanyProfiles ON PublishedIncidents.CompanyId = CompanyProfiles.CompanyId
		WHERE  (CompanyProfiles.CompanyId IS NULL)   
	
		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete incidents that do not belong to current company profiles', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		IF (@LastUpdate IS NULL) BEGIN
			TRUNCATE TABLE [dbo].[PublishedIncidents] END 
		ELSE BEGIN
			DELETE PublishedIncidents FROM dbo.PublishedIncidents AS PublishedIncidents
			INNER JOIN @SelectedCompanies AS SelectedCompanies ON PublishedIncidents.CompanyId = SelectedCompanies.Id
		END

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete incidents that are going to be updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		INSERT INTO [dbo].[PublishedIncidents]
    	EXECUTE [Test_Events].[dbo].[spGetIncidentsByCompanyIds2] @CompanyIds, @MaxLastUpdate

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Incidents have been updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		--===== Alerts ========================================================================

		TRUNCATE TABLE [dbo].[Alerts]
		INSERT INTO [dbo].[Alerts]
		EXECUTE [Test_Events].[dbo].[spGetAlerts]

		set @rowsAffected = @@ROWCOUNT

		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Extract Alerts', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		
		--===== News Articles ========================================================================

		TRUNCATE TABLE [dbo].[NewsArticles]
		INSERT INTO [dbo].[NewsArticles]
		EXECUTE [Test_Events].[dbo].[spGetNewsArticles]

		set @rowsAffected = @@ROWCOUNT

		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Extract News Articles', @elapsedTime, @rowsAffected)
		select @startTime = getdate()
	END


	select @endTime = getdate()
	select @elapsedTime = datediff(second, @totalTime, @endTime)
	print 'SnapshotData completed successfully => Total elapsed time:' +  str(@elapsedTime)
	insert into @Log (Description, ElapsedTime, RowsAffected)
	values ('SnapshotData completed successfully', @elapsedTime, 0)


	insert into [dbo].[SyncLog] ([LastSyncDate], [ElapsedTime], [LastUpdate])
	values (GETUTCDATE(), @elapsedTime, @MaxLastUpdate)
	

	COMMIT TRANSACTION;	


	select * from @Log

END
GO


